﻿using System;
using System.Web;
using System.Data;
using System.Collections;
using System.Data.SqlClient;

namespace EFB.Lib.Repository {

    #region History
    /// <summary>
    /// Author     : Carlos Duarte - cebduarte@gmail.com
    /// Date       : 14/04/2012
    /// Description: Class helper in communication with the database.
    /// </summary>
    #endregion
    public class SqlHelper {

        #region Attributes

        private SqlConnection Connection;
        private SqlTransaction Transaction;

        private static SqlHelper SqlInstance;

        public static SqlHelper Instance {
            get {
                SqlHelper Result = null;
                Result = ((SqlHelper)ContextHelper.Item("$SqlHelper.Instance"));
                if (Result == null) {
                    Result = new SqlHelper(ContextHelper.ConnectionStringItem("SqlHelper.ConnectionString"));
                    ContextHelper.WriteItem("$SqlHelper.Instance", Result);
                }
                return Result;
            }
        }

        public Boolean IsTransactionActive {
            get {
                return (Transaction != null && Transaction.Connection != null);
            }
        }

        #endregion

        #region Constructors

        public SqlHelper(String ConnectionString) {
            Connection = new SqlConnection();
            Connection.ConnectionString = ConnectionString;
        }

        #endregion

        #region Methods

        public void Open() {
            if (Connection.State == ConnectionState.Closed) {
                Connection.Open();
            }
        }

        public void Close() {            
            if (Connection.State != ConnectionState.Closed) {
                Rollback();
                Connection.Close();
            }
        }

        public void BeginTransaction() {
            Rollback();
            Transaction = Connection.BeginTransaction();
        }

        public void Commit() {
            if (IsTransactionActive) {
                Transaction.Commit();
            }
            Transaction = null;
        }

        public void Rollback() {
            if (IsTransactionActive) {
                Transaction.Rollback();
            }
            Transaction = null;
        }

        public SqlCommand CreateCommand(String CommandText, SqlParameter[] Parameters) {
            SqlCommand Command = Connection.CreateCommand();
            //Command.CommandTimeout = Convert.ToInt32(ContextHelper.AppSettings("CommandTimeout"));
            Command.Transaction = Transaction;
            Command.CommandType = CommandType.Text;
            Command.CommandText = CommandText;
            if (Parameters.Length > 0) {
                Command.Parameters.AddRange(Parameters);
            }
            return Command;
        }

        public DataTable ExecuteReader(String CommandText) {
            return ExecuteReader(CommandText, new SqlParameter[] { });
        }

        public DataTable ExecuteReader(String CommandText, SqlParameter[] Parameters) {
            DataTable TableResult = new DataTable();
            SqlDataReader DataReader = null;
            try {
                DataReader = CreateCommand(CommandText, Parameters).ExecuteReader();
                TableResult.Load(DataReader);
            } finally {                
                DataReader = null;
            }
            return TableResult;
        }

        public Object ExecuteScalar(String CommandText) {
            return ExecuteScalar(CommandText, new SqlParameter[] { });
        }

        public Object ExecuteScalar(String CommandText, SqlParameter[] Parameters) {
            SqlCommand Command = null;
            try {
                Command = CreateCommand(CommandText, Parameters);
                return Command.ExecuteScalar();
            } finally {
                Command = null;
            }
        }

        public Int32 ExecuteNonQuery(String CommandText) {
            return ExecuteNonQuery(CommandText, new SqlParameter[] { });
        }

        public Int32 ExecuteNonQuery(String CommandText, SqlParameter[] Parameters) {
            SqlCommand Command = null;
            try {
                Command = CreateCommand(CommandText, Parameters);
                return Command.ExecuteNonQuery();
            } finally {
                Command = null;
            }
        }

        #endregion

    }

}